﻿Imports Newtonsoft.Json

Public Class EmployeeManager

    Private manager As SQLManager

    Public Sub New()
        manager = New SQLManager
    End Sub

    Public Function getAllEmployees() As List(Of Employee)
        Dim empArr As New List(Of Employee)
        Dim query As String = "SELECT * FROM tbl_employee"
        Dim resultString As String = "" + manager.executeQuery(query)

        empArr = JsonConvert.DeserializeObject(Of List(Of Employee))(resultString)

        If (resultString = "") Then
            'MsgBox("NO DATA")
        End If

       


        Return empArr
    End Function

    Public Function retrieveEmployeeById(ByVal id As Integer) As Employee
        Dim empArr As New List(Of Employee)
        Dim emp As New Employee
        Dim query As String = "SELECT * FROM tbl_employee WHERE id='" + id.ToString + "'"
        Dim resultString As String = "" + manager.executeQuery(query)

        empArr = JsonConvert.DeserializeObject(Of List(Of Employee))(resultString)

        If (resultString = "") Then
            MsgBox("NO DATA")
        End If

        For Each tempEmp As Employee In empArr
            emp = tempEmp
            Exit For
        Next


        Return emp
    End Function


    Public Function updateEmployee(ByVal empObj As Employee)

        Dim query As String = "UPDATE `payroll`.`tbl_employee` SET `username` = '" + empObj.username + "', `password` = '" + empObj.password + "' WHERE `tbl_employee`.`id` = '" + empObj.id.ToString + "'"
        manager.executeUpdate(query)
    End Function

    Public Function updateEmployeeComplete(ByVal empObj As Employee)

        Dim query As String = "UPDATE `payroll`.`tbl_employee` SET `username` = '" + empObj.username + "', `password` = '" + empObj.password + "', `role` = '" + empObj.role.ToString + "', `salary` = '" + empObj.salary.ToString + "', `time_in` = '" + empObj.time_in.ToString + "', `time_out` = '" + empObj.time_out.ToString + "',`flexitime` = '" + empObj.flexitime + "' WHERE `tbl_employee`.`id` = '" + empObj.id.ToString + "'"
        manager.executeUpdate(query)
    End Function

    Public Function insertEmployee(ByVal empObj As Employee) As Employee

        Dim query As String = "INSERT INTO `payroll`.`tbl_employee` (`id`, `username`, `password`, `role`, `salary`, `time_in`, `time_out`,`flexitime`) VALUES (NULL, '" + empObj.username + "', '" + empObj.password + "', '" + empObj.role.ToString + "', '" + empObj.salary.ToString + "', '" + empObj.time_in.ToString + "', '" + empObj.time_out.ToString + "','" + empObj.flexitime + "')"
        manager.executeUpdate(query)

        Dim empArr As New List(Of Employee)
        Dim emp As Employee
        Dim query2 As String = "SELECT * FROM `payroll`.`tbl_employee` ORDER BY id DESC LIMIT 0, 1"
        Dim resultString As String = "" + manager.executeQuery(query2)

        empArr = JsonConvert.DeserializeObject(Of List(Of Employee))(resultString)

      

        For Each tempEmp As Employee In empArr
            emp = tempEmp
            Exit For
        Next


        Return emp

    End Function

    Function deleteItemEmployee(ByVal id As String) As Integer
        Dim empinfomgr As New EmployeeInfoManager
        empinfomgr.deleteEmpInfoByEmployeeID(id)
        Dim query As String = "DELETE FROM `payroll`.`tbl_employee_info` WHERE `tbl_employee_info`.`employee_id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
        query = "DELETE FROM `payroll`.`log_biometrics` WHERE `log_biometrics`.`employee_id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
        query = "DELETE FROM `payroll`.`tbl_biometrics` WHERE `tbl_biometrics`.`employee_id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
        query = "DELETE FROM `payroll`.`tbl_leave` WHERE `tbl_leave`.`employee_id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
        query = "DELETE FROM `payroll`.`tbl_overtime` WHERE `tbl_overtime`.`employee_id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
        query = "DELETE FROM `payroll`.`tbl_payroll` WHERE `tbl_payroll`.`employee_id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
        query = "DELETE FROM `payroll`.`tbl_employee` WHERE `tbl_employee`.`id` = '" + id.ToString + "'"
        manager.executeUpdate(query)
    End Function


End Class
